﻿/*
Desciption:
Will enable\disable (default) all SQL Agent jobs or those that belong to the specified category
and wait for any running jobs to complete or for the maximum wait duration to be exceeded.
*/
CREATE PROCEDURE [util].[SetJobStatus](
	@JobCategoryName sysname = NULL,
	@JobStatus bit = 0,
	@MaxWaitInMinutes int = 5
)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @DisableJobCmd nvarchar(2000) = (
		SELECT
			N'EXEC [msdb].[dbo].[sp_update_job] @job_id='''+CAST(j.[job_id] AS nvarchar(36)) + N''', @Enabled=' + CAST(@JobStatus AS nchar(1)) + ';'
		FROM
			[msdb].dbo.sysjobs AS j
		INNER JOIN [msdb].[dbo].[syscategories] AS c
			ON (c.[category_id] = j.[category_id])
			AND(c.[name] = ISNULL(@JobCategoryName, c.[name]))
		FOR XML PATH('')
	)
	EXEC(@DisableJobCmd);
	DECLARE @WaitStartTime datetime = GETDATE(), @MaxWaitExceeded bit = 1;
	WHILE (DATEDIFF(MINUTE, @WaitStartTime, GETDATE()) < @MaxWaitInMinutes)
		BEGIN
			DECLARE @RunningJobs TABLE (   
				[Job_ID] UNIQUEIDENTIFIER, [Last_Run_Date] INT, [Last_Run_Time] INT, [Next_Run_Date] INT,
				[Next_Run_Time] INT, [Next_Run_Schedule_ID] INT, [Requested_To_Run] INT, [Request_Source] INT,
				[Request_Source_ID] VARCHAR(100), [Running] INT, [Current_Step] INT, [Current_Retry_Attempt] INT, [State] INT
			);
			DELETE @RunningJobs;    
			INSERT INTO @RunningJobs EXEC master.dbo.xp_sqlagent_enum_jobs 1, garbage   
  
			IF NOT EXISTS(
				SELECT *
				FROM @RunningJobs AS s
				INNER JOIN [msdb].dbo.sysjobs AS j
					ON (j.[job_id] = s.[Job_ID])
				INNER JOIN [msdb].[dbo].[syscategories] AS c
					ON (c.[category_id] = j.[category_id])
					AND(c.[name] = ISNULL(@JobCategoryName, c.[name]))
				WHERE (s.[Running] = 1) 
			)
				BEGIN
					PRINT 'SQL Agent jobs completed.';
					SET @MaxWaitExceeded = 0;
					BREAK;
				END
			ELSE
				BEGIN
					PRINT 'Waiting for SQL Agent jobs to complete...';
					WAITFOR DELAY '00:00:10';
				END
		END

	IF (@MaxWaitExceeded = 1)
		BEGIN
			DECLARE @ExceptionMessage nvarchar(2000) = N'The maximum wait time has been exceeded. Please check SQL Agent job status manually.';
			THROW 50001, @ExceptionMessage, 1;
		END
END